﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using VSDBM.Code.dto;
using System.Data;
using MySql.Data.MySqlClient;


namespace VSDBM.Code.dao
{
    public class UserDAO
    {

        public static String add(User user)
        {
            try
            {
                MySqlConnection connection = Connection.getConnection();

                DataSet ds = select(false, " WHERE login = '" + user.login + "'", "");
                if (ds == null || ds.Tables[0].Rows.Count == 0)
                {
                    MySqlCommand query = new MySqlCommand("INSERT INTO users (name,email,login,password,acessgroup,status) VALUES ('" + user.name + "','" + user.email + "','" + user.login + "','" + user.password + "'," + user.acessgroup + ",1)", connection);
                    query.ExecuteNonQuery();
                    return "Cadastrado com sucesso!";
                }

                return "Login já existe";
            }
            catch (Exception e)
            {
                return "ERRO! - " + e.Message;
            }
        }

        public static Boolean edit(User user)
        {
            try
            {
                MySqlConnection connection = Connection.getConnection();
                MySqlCommand query = new MySqlCommand("UPDATE users SET name='" + user.name + "',email='" + user.email + "',login='" + user.login + "',password='" + user.password + "',acessgroup='" + user.acessgroup + "', status='" + user.status + "' WHERE id = " + user.id + "", connection);
                query.ExecuteNonQuery();
                return true;
            }
            catch (Exception e)
            {
                return false;
            }
        }


        public static Boolean delete(User user)
        {
            try
            {
                MySqlConnection connection = Connection.getConnection();
                MySqlCommand query = new MySqlCommand("UPDATE users SET status = 0 WHERE id = " + user.id + "", connection);
                query.ExecuteNonQuery();
                return true;
            }
            catch (Exception e)
            {
                return false;
            }
        }


        public static DataSet select(Boolean campname, String filters, String orderby)
        {

            DataSet dSet = new DataSet();
            try
            {
                MySqlConnection connection = Connection.getConnection();
                MySqlDataAdapter adapter = new MySqlDataAdapter();
                if (campname)
                {
                    adapter.SelectCommand = new MySqlCommand("SELECT  id as ID, name as Name, email as Email, login as Login, acessgroup as 'Acess Group'  FROM users " + filters + " " + orderby, connection);
                }
                else
                {
                    adapter.SelectCommand = new MySqlCommand("SELECT  * FROM users " + filters + " " + orderby, connection);
                }
                adapter.Fill(dSet);
                return dSet;
            }
            catch (Exception e)
            {
                return dSet;
            }
        }


        public static User Fill(int id, String name, String email, String login, String password, int acessgroup, int status)
        {
            User user = new User();
            user.id = id;
            user.name = name;
            user.email = email;
            user.login = login;
            user.password = password;
            user.acessgroup = acessgroup;
            user.status = status;


            return user;
        }


        public static List<User> DStoList(DataSet ds)
        {
            List<User> list = new List<User>();
            try
            {
                for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
                {
                    DataTable registro = ds.Tables[0];
                    list.Add(Fill(int.Parse(registro.Rows[i]["id"].ToString()), registro.Rows[i]["name"].ToString(), registro.Rows[i]["email"].ToString(), registro.Rows[i]["login"].ToString(), registro.Rows[i]["password"].ToString(), int.Parse(registro.Rows[i]["acessgroup"].ToString()), int.Parse(registro.Rows[i]["status"].ToString())));
                }

            }
            catch (Exception e) { }
            return list;
        }


        public static Boolean resetPassword(User user)
        {
            try
            {
                MySqlConnection connection = Connection.getConnection();
                MySqlCommand query = new MySqlCommand("UPDATE users SET password='" + Code.functions.Encryption.CalculateMD5Hash("123") + "' WHERE id = " + user.id + "", connection);
                query.ExecuteNonQuery();
                return true;
            }
            catch (Exception e)
            {
                return false;
            }
        }
    }
}
